package com.vnas.fragment;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.TextView;

import com.vnas.database.FonanDatabase;
import com.vnas.fonan.R;
import com.vnas.utils.Date;
import com.vnas.utils.Utils;

public class FragmentOverView extends MyFragment {

	TextView tvWelcome, tvBalance, tvExpense, tvIncome, tvPrevIn, tvPrevEx,
			tvPrevNetIn;

	Button btnAddIn, btnAddEx;
	FonanDatabase fdb;
	
	String username = "";

	public FragmentOverView() {
		super();
		title = "OverView";
		fdb = new FonanDatabase(getActivity());
	}

	@Override
	public void onCreate(Bundle savedInstanceState) {

		super.onCreate(savedInstanceState);
		SQLiteDatabase db = fdb.getSQLiteDatabase();
		Date d = new Date().getPrevMonth();
		if(checkNewMonth(db, d.getMonth(), d.getYear()))
		{
			insertReport(db);
			updateAccountTableByMonth(db);
		}
		
	}

	private boolean checkNewMonth(SQLiteDatabase db, int m, int y)
	{
		String sql = "select * from report_by_month where month = " + m + " " +
				"and year = " + y;
		
		Cursor c = db.rawQuery(sql, null);
		if(c.moveToFirst())// bao cao da duoc ghi truoc do
			return false;
		return true;
	}
	@Override
	public View onCreateView(LayoutInflater inflater, ViewGroup container,
			Bundle savedInstanceState) {
		View v = inflater.inflate(R.layout.fragment_overview, container, false);
		tvWelcome = (TextView) v.findViewById(R.id.tv_welcome);
		tvBalance = (TextView) v.findViewById(R.id.tv_balance);
		tvExpense = (TextView) v.findViewById(R.id.tv_expense);
		tvIncome = (TextView) v.findViewById(R.id.tv_income);
		tvPrevEx = (TextView) v.findViewById(R.id.tv_prev_expense);
		tvPrevIn = (TextView) v.findViewById(R.id.tv_prev_income);
		tvPrevNetIn = (TextView) v.findViewById(R.id.tv_prev_net_income);

		btnAddEx = (Button) v.findViewById(R.id.btn_add_expense);
		btnAddIn = (Button) v.findViewById(R.id.btn_add_income);

		btnAddEx.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {

			}
		});

		btnAddIn.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {

			}
		});
		fillData();
		return v;
	}

	public void setUsername(String u) {
		username = u;
	}

	public void fillData() {
		tvWelcome.setText("Hi " + username);
		
		String query = "select balance from account where name = '" + username
				+ "'";
		SQLiteDatabase db = fdb.getSQLiteDatabase();
		Cursor c = db.rawQuery(query, null);

		if (c.moveToFirst()) {
			tvBalance.setText(c.getString(0) + " vnd");
		}

		query = "select amount from expenses where " + "day = "
				+ Utils.getCurDay() + " and month = " + Utils.getCurMonth()
				+ " and year = " + Utils.getCurYear();
		c = db.rawQuery(query, null);
		int e = 0;
		if (c.moveToFirst()) {
			do {
				int temp = c.getInt(0);
				e += temp;

			} while (c.moveToNext());

		}
		tvExpense.setText(e + " vnd");

		query = "select amount from income where " + " month = "
				+ Utils.getCurMonth() + " and year = " + Utils.getCurYear();
		c = db.rawQuery(query, null);
		e = 0;
		if (c.moveToFirst()) {
			do {
				int temp = c.getInt(0);
				e += temp;

			} while (c.moveToNext());

		}
		tvIncome.setText(e + " vnd");

		int[] re = getPrevInfo(db, Utils.getCurMonth() - 1, Utils.getCurYear());

		tvPrevIn.setText(re[0] + " vnd");
		tvPrevEx.setText(re[1] + " vnd");
		tvPrevNetIn.setText(re[2] + " vnd");
		c.close();
		db.close();
		fdb.close();

	}

	/**
	 * This method will update new balance when the income and expense field
	 * change.
	 * 
	 * @param newEx
	 * @param newIn
	 */
	public void updateBalance(int newEx, int newIn) throws SQLException {
		int newBal = newIn - newEx;

		String sql = "update account set balance= " + newBal
				+ " where name = '" + username + "'";

		SQLiteDatabase db = fdb.getSQLiteDatabase();

		db.execSQL(sql);
		db.close();
		fdb.close();
	}

	public void updateNewIncome(SQLiteDatabase db, int newIn, String un) {
		String sql = "update account set income = " + newIn + " where name = '"
				+ un + "'";
		db.execSQL(sql);

	}

	public void updateNewExpense(SQLiteDatabase db, int newEx, String un) {
		String sql = "update account set income = " + newEx + " where name = '"
				+ un + "'";
		db.execSQL(sql);

	}

	/**
	 * this method returns an array
	 * 
	 * @param db
	 * @param m
	 * @param y
	 * @return
	 * @throws SQLException
	 */

	public int[] getPrevInfo(SQLiteDatabase db, int m, int y)
			throws SQLException {
		int[] re = new int[3];
		String sql = "select income, expense, netincome from report_by_month where month = "
				+ m + " and year = " + y;

		Cursor c = db.rawQuery(sql, null);
		if (c.moveToFirst()) {
			re[0] = c.getInt(0);// income
			re[1] = c.getInt(1);// expense
			re[2] = c.getInt(2);// netincome
		}
		return re;
	}

	public int getNewExpenseID(SQLiteDatabase db) {
		String sql = "select * from expense";
		Cursor c = db.rawQuery(sql, null);
		return c.getCount();
	}

	public int getNewIncomeID(SQLiteDatabase db) {
		String sql = "select * from income";
		Cursor c = db.rawQuery(sql, null);
		return c.getCount();
	}

	public int getTotalIncomeInMonth(SQLiteDatabase db, int m, int y) {
		String query = "select amount from income where " + " month = "
				+ Utils.getCurMonth() + " and year = " + Utils.getCurYear();
		Cursor c = db.rawQuery(query, null);
		int e = 0;
		if (c.moveToFirst()) {
			do {
				int temp = c.getInt(0);
				e += temp;

			} while (c.moveToNext());
		}
		return e;

	}

	public int getTotalExpenseInMonth(SQLiteDatabase db, int m, int y) {
		String query = "select amount from expense where " + " month = "
				+ Utils.getCurMonth() + " and year = " + Utils.getCurYear();
		Cursor c = db.rawQuery(query, null);
		int e = 0;
		if (c.moveToFirst()) {
			do {
				int temp = c.getInt(0);
				e += temp;

			} while (c.moveToNext());
		}
		return e;

	}

	public void insertReport(SQLiteDatabase db) throws SQLException {
		Date d = new Date().getPrevMonth();// Current date

		int m = d.getMonth();
		int y = d.getYear();

		int in = getTotalIncomeInMonth(db, m, y);
		int ex = getTotalIncomeInMonth(db, m, y);
		int netIn = in - ex;
		String sql = "insert into report_by_month(month, year, expense, income, netincome) "
				+ "values("
				+ m
				+ ", "
				+ y
				+ ", "
				+ ex
				+ ", "
				+ in
				+ ", "
				+ netIn + ")";

		db.execSQL(sql);
	}

	public void updateAccountTableByMonth(SQLiteDatabase db) throws SQLException{
		// update balance in account table
		Date d = new Date().getPrevMonth();// Current date

		int m = d.getMonth();
		int y = d.getYear();

		// update saving account = cur saving + prev balance.

		int sA = getTotalSaving(db, username) + getPrevBalance(db, m, y);
		String sql = "update account set balance = 0, saving = " + sA + " "
				+ ", total = " + sA + " where 1";
		db.execSQL(sql);
	}

	public int getTotalSaving(SQLiteDatabase db, String un) {
		String sql = "select saving from account where name = '" + un + "'";
		Cursor c = db.rawQuery(sql, null);
		int s = 0;
		if (c.moveToFirst()) {
			s = c.getInt(0);
		}
		return s;
	}

	// Get previous balance ( netincome of prev month).
	public int getPrevBalance(SQLiteDatabase db, int prevM, int prevY) {
		String sql = "select netincome from report_by_month where month = "
				+ prevM + " and year = " + prevY;
		Cursor c = db.rawQuery(sql, null);
		int s = 0;
		if (c.moveToFirst()) {
			s = c.getInt(0);
		}
		return s;
	}

	@Override
	public void onDestroy() {
		fdb.close();
		super.onDestroy();
	}

}
